Writing Recursive CTEs and Understanding Their Use Cases in MySQL

Course: MySQL Training

Topic: Writing Recursive CTEs and Understanding Their Use Cases

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Recursive CTEs

A Recursive Common Table Expression (CTE) is an SQL feature that allows a query to refer to its own result while generating additional rows. In MySQL, recursive CTEs are commonly used to traverse hierarchical data, generate number sequences, create date ranges, explore employee reporting structures, and process relationships that involve multiple levels.

A CTE is a named temporary result set available within a single SQL statement. A recursive CTE extends this concept by repeatedly using previously generated rows to produce new rows until a termination condition is reached or an execution limit is encountered.

For example, suppose an organization stores employees and their managers. An employee may report to a manager who reports to another manager. A recursive CTE can start with a particular employee and retrieve their manager, the manager's manager, and so on.

Recursive CTEs are supported in MySQL 8.0 and later. If you are using an earlier MySQL version, you may need an alternative approach, such as application-level iteration or a different database design.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand Common Table Expressions and recursive queries.
• Differentiate between regular CTEs and recursive CTEs.
• Identify the anchor member and recursive member of a CTE.
• Write recursive CTEs using WITH RECURSIVE.
• Generate number sequences and date ranges.
• Traverse employee-manager hierarchies and organizational structures.
• Understand recursion termination conditions and recursion limits.
• Use recursive CTEs to explore hierarchical categories and product structures.
• Identify common recursive query errors and performance issues.
• Apply recursive CTEs to practical database problems.

3. Understanding Common Table Expressions

3.1 What Is a CTE?

A Common Table Expression is a named query result defined using the WITH clause. It can be referenced within the SQL statement in which it is defined.

A regular CTE improves readability by separating a query into logical parts. It is not a permanently stored table and does not need to be manually created or dropped.

3.2 Basic CTE Syntax

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

In this example:

• WITH introduces the CTE definition.
• cte_name is the name assigned to the query result.
• The query inside parentheses produces the CTE result.
• The outer query retrieves data from the CTE.

3.3 Example of a Regular CTE

WITH high_salary_employees AS (
    SELECT employee_id, employee_name, salary
    FROM employees
    WHERE salary > 60000
)
SELECT employee_name, salary
FROM high_salary_employees;

This query first defines a CTE containing employees earning more than 60,000. The outer query then retrieves the names and salaries from that result.

This is a non-recursive CTE because it does not refer to itself.

4. What Is a Recursive CTE?

A recursive CTE is a CTE whose definition refers to its own name. It generates an initial result and then uses previously generated rows to produce additional results.

The recursive process continues until the recursive query produces no further rows or execution is stopped by a configured limit or another termination mechanism.

4.1 Basic Syntax

WITH RECURSIVE cte_name AS (
    -- Anchor member
    SELECT initial_value

    UNION ALL

    -- Recursive member
    SELECT next_value
    FROM cte_name
    WHERE termination_condition
)
SELECT *
FROM cte_name;

The query has three important parts:

1. Anchor member: Produces the initial row or rows. It does not refer to the recursive CTE itself.
2. Recursive member: Refers to the CTE and generates additional rows based on previously generated results.
3. Termination condition: Determines when the recursive member stops generating additional rows.

4.2 Why Is RECURSIVE Required?

The keyword RECURSIVE tells MySQL that the CTE definition can refer to itself. Without it, a self-reference may be interpreted as a reference to a table that does not exist, resulting in an error.

5. How Recursive CTEs Work

A recursive CTE can be understood as a repeated process that begins with an initial result and uses that result to generate the next set of rows.

5.1 Execution Process

1. MySQL evaluates the anchor member to create the initial result.
2. The recursive member processes rows from the current recursive iteration.
3. The recursive member generates the next set of rows.
4. The process continues using newly generated rows.
5. When the recursive member produces no additional rows, recursion ends.
6. The outer query retrieves the resulting rows.

5.2 Recursive CTE Flow

Start
  |
  v
Execute Anchor Member
  |
  v
Generate Initial Rows
  |
  v
Execute Recursive Member
  |
  v
Generate Additional Rows
  |
  v
Are More Rows Produced?
  |                  |
 Yes                  No
  |                   |
  v                   v
Continue Recursion   Stop
                      |
                      v
                 Return Results

5.3 Important Technical Detail

Each recursive iteration operates on rows produced by the previous iteration. The recursive member does not simply rerun the anchor query from the beginning on every iteration.

The database optimizer may choose execution strategies that affect physical processing, but the anchor-and-recursive-member model is useful for understanding the query's logical behavior.

6. First Recursive CTE Example: Generate Numbers

One of the simplest applications of a recursive CTE is generating a sequence of numbers without requiring a permanent numbers table.

6.1 Generate Numbers From 1 to 10

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM number_sequence
    WHERE n < 10
)
SELECT n
FROM number_sequence;

6.2 Expected Output

6.3 Explanation

• The anchor member starts the sequence with the value 1.
• The recursive member adds 1 to the current value.
• The condition n < 10 prevents another row from being generated after 10.
• The outer query returns the generated sequence.

When the recursive member processes the row containing 10, the condition is false, so it produces no further rows and recursion terminates.

6.4 Generate Numbers From 1 to 100

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM number_sequence
    WHERE n < 100
)
SELECT n
FROM number_sequence
ORDER BY n;

This query generates the integers from 1 through 100. Use an appropriate upper bound to prevent unnecessary work.

7. Generating a Sequence With a Custom Starting Value

A recursive CTE can generate sequences beginning at a value other than 1. The anchor member defines the starting point, and the recursive member determines how the value changes.

7.1 Generate Numbers From 5 to 15

WITH RECURSIVE number_sequence AS (
    SELECT 5 AS n

    UNION ALL

    SELECT n + 1
    FROM number_sequence
    WHERE n < 15
)
SELECT n
FROM number_sequence;

7.2 Generate Even Numbers

WITH RECURSIVE even_numbers AS (
    SELECT 2 AS n

    UNION ALL

    SELECT n + 2
    FROM even_numbers
    WHERE n < 20
)
SELECT n
FROM even_numbers;

This generates the even numbers from 2 through 20.

7.3 Generate Odd Numbers

WITH RECURSIVE odd_numbers AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 2
    FROM odd_numbers
    WHERE n < 19
)
SELECT n
FROM odd_numbers;

This generates the odd numbers from 1 through 19.

8. Generating Date Ranges With Recursive CTEs

Recursive CTEs can generate continuous date ranges. This is useful when a report needs every date in a period, including dates for which no transactions were recorded.

8.1 Generate Dates Between Two Fixed Dates

WITH RECURSIVE date_series AS (
    SELECT DATE('2026-01-01') AS calendar_date

    UNION ALL

    SELECT calendar_date + INTERVAL 1 DAY
    FROM date_series
    WHERE calendar_date < DATE('2026-01-07')
)
SELECT calendar_date
FROM date_series
ORDER BY calendar_date;

8.2 Expected Output

8.3 Explanation

1. The anchor member creates the first date.
2. The recursive member adds one day to the current date.
3. The condition permits another date to be generated while the current date is earlier than January 7.
4. The final generated date is January 7, after which recursion stops.

8.4 Practical Use Cases for Date Generation

• Generating dates for daily sales reports.
• Creating calendar ranges for attendance systems.
• Building daily website traffic reports.
• Identifying missing dates in transaction records.
• Preparing daily inventory and order summaries.
• Filling date gaps in financial and operational dashboards.

9. Recursive CTEs for Hierarchical Data

Hierarchical data represents records arranged in parent-child relationships. Examples include organizational structures, category trees, folder systems, product classifications, and reporting relationships.

A common relational design stores the parent record's identifier in a column of the child record. For example, an employee record may store the employee ID of their manager.

9.1 Sample Employee Hierarchy

Anita is the top-level manager. Rahul and Priya report to Anita. Aman and Neha report to Rahul, while Vikram reports to Priya.

9.2 Create the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    manager_id INT NULL,
    FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
);

INSERT INTO employees
    (employee_id, employee_name, manager_id)
VALUES
    (1, 'Anita', NULL),
    (2, 'Rahul', 1),
    (3, 'Priya', 1),
    (4, 'Aman', 2),
    (5, 'Neha', 2),
    (6, 'Vikram', 3);

The foreign key establishes a relationship between each employee and their manager. The top-level employee has a NULL manager ID.

10. Finding All Employees Under a Manager

A recursive CTE can retrieve every employee who reports directly or indirectly to a particular manager.

10.1 Find All Employees Under Anita

WITH RECURSIVE employee_hierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS hierarchy_level
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.hierarchy_level + 1
    FROM employees AS e
    JOIN employee_hierarchy AS eh
        ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    hierarchy_level
FROM employee_hierarchy
ORDER BY hierarchy_level, employee_id;

10.2 Expected Output

10.3 Explanation

• The anchor member selects Anita using employee_id = 1.
• The recursive member joins employees to the hierarchy using e.manager_id = eh.employee_id.
• First, it retrieves employees who report directly to Anita.
• Next, it retrieves employees who report to those employees.
• The hierarchy level increases by one for each level of the organization.

The recursive member continues until it cannot find additional employees whose manager ID matches an employee in the current recursive result.

11. Finding All Managers Above an Employee

Recursive CTEs can also traverse a hierarchy in the opposite direction. Instead of finding all descendants of a manager, the query can find the management chain above a particular employee.

11.1 Find the Management Chain for Aman

WITH RECURSIVE management_chain AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS hierarchy_level
    FROM employees
    WHERE employee_id = 4

    UNION ALL

    SELECT
        m.employee_id,
        m.employee_name,
        m.manager_id,
        mc.hierarchy_level + 1
    FROM employees AS m
    JOIN management_chain AS mc
        ON m.employee_id = mc.manager_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    hierarchy_level
FROM management_chain
ORDER BY hierarchy_level;

11.2 Expected Output

The query starts with Aman, finds Rahul as the manager, and then finds Anita as Rahul's manager. The process ends when the current record has no manager.

12. Recursive CTEs for Product Categories

Online stores often organize products into nested categories and subcategories. For example, Electronics may contain Computers, and Computers may contain Laptops.

12.1 Sample Category Table

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_category_id INT NULL,
    FOREIGN KEY (parent_category_id)
        REFERENCES categories(category_id)
);

INSERT INTO categories
    (category_id, category_name, parent_category_id)
VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Laptops', 2),
    (4, 'Desktops', 2),
    (5, 'Mobile Devices', 1),
    (6, 'Smartphones', 5);

12.2 Retrieve Every Subcategory Under Electronics

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_category_id,
        0 AS category_level
    FROM categories
    WHERE category_id = 1

    UNION ALL

    SELECT
        c.category_id,
        c.category_name,
        c.parent_category_id,
        ct.category_level + 1
    FROM categories AS c
    JOIN category_tree AS ct
        ON c.parent_category_id = ct.category_id
)
SELECT
    category_id,
    category_name,
    parent_category_id,
    category_level
FROM category_tree
ORDER BY category_level, category_id;

This query starts with Electronics and finds every category below it, including Computers, Laptops, Desktops, Mobile Devices, and Smartphones.

12.3 Use Cases for Category Trees

• Displaying nested product categories.
• Finding all descendants of a selected category.
• Building navigation menus.
• Calculating category depth.
• Retrieving every nested folder or classification.

13. Recursive CTEs for Bill of Materials

A Bill of Materials (BOM) describes the components needed to assemble a product. A finished product can contain subassemblies, which may themselves contain additional components.

A recursive CTE can explore these multilevel relationships and retrieve the components required throughout the structure.

13.1 Sample BOM Table

CREATE TABLE product_components (
    parent_product_id INT NOT NULL,
    component_product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (parent_product_id, component_product_id)
);

INSERT INTO product_components
    (parent_product_id, component_product_id, quantity)
VALUES
    (100, 200, 2),
    (100, 300, 1),
    (200, 400, 3),
    (200, 500, 2);

In this example, product 100 requires two units of product 200 and one unit of product 300. Each unit of product 200 requires three units of product 400 and two units of product 500.

13.2 Traverse the Component Structure

WITH RECURSIVE bom_tree AS (
    SELECT
        parent_product_id,
        component_product_id,
        quantity,
        1 AS component_level
    FROM product_components
    WHERE parent_product_id = 100

    UNION ALL

    SELECT
        pc.parent_product_id,
        pc.component_product_id,
        pc.quantity,
        bt.component_level + 1
    FROM product_components AS pc
    JOIN bom_tree AS bt
        ON pc.parent_product_id = bt.component_product_id
)
SELECT
    parent_product_id,
    component_product_id,
    quantity,
    component_level
FROM bom_tree
ORDER BY component_level, parent_product_id, component_product_id;

This query explores components under product 100 and then finds components belonging to each discovered subassembly. The example demonstrates traversal; calculating total quantities across repeated subassemblies requires multiplying quantities along each path and then aggregating the resulting component quantities.

14. Recursive CTEs for Graph-Like Relationships

Some data structures contain relationships that are not strict trees. Examples include route networks, dependency chains, social connections, and directed links between records.

A recursive CTE can follow these links from an initial record and discover reachable records. However, graph traversal requires special attention to cycles, duplicate paths, and the intended meaning of reachability.

14.1 Example: Traversing a Simple Dependency Table

CREATE TABLE task_dependencies (
    task_id INT NOT NULL,
    depends_on_task_id INT NOT NULL,
    PRIMARY KEY (task_id, depends_on_task_id)
);

INSERT INTO task_dependencies
    (task_id, depends_on_task_id)
VALUES
    (2, 1),
    (3, 2),
    (4, 2),
    (5, 3);

14.2 Find Tasks That Depend Directly or Indirectly on Task 1

WITH RECURSIVE dependent_tasks AS (
    SELECT
        task_id,
        depends_on_task_id,
        1 AS dependency_level
    FROM task_dependencies
    WHERE depends_on_task_id = 1

    UNION ALL

    SELECT
        td.task_id,
        td.depends_on_task_id,
        dt.dependency_level + 1
    FROM task_dependencies AS td
    JOIN dependent_tasks AS dt
        ON td.depends_on_task_id = dt.task_id
)
SELECT
    task_id,
    depends_on_task_id,
    dependency_level
FROM dependent_tasks
ORDER BY dependency_level, task_id;

This query starts with tasks that directly depend on task 1, then discovers tasks that depend on those tasks. The pattern can be used to investigate dependency chains, provided the data model and cycle-handling rules are appropriate.

15. Generating a Fibonacci Sequence

A recursive CTE can generate sequences in which each new value depends on values from previous rows. The Fibonacci sequence is a useful demonstration because each number is the sum of the two preceding numbers.

15.1 SQL Query

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1

    UNION ALL

    SELECT
        n + 1,
        next_fib_n,
        fib_n + next_fib_n
    FROM fibonacci
    WHERE n < 10
)
SELECT n, fib_n
FROM fibonacci
ORDER BY n;

15.2 Expected Output

The CTE maintains the current Fibonacci number and the next Fibonacci number. During each recursive iteration, the next number becomes the current number, and the new next number is calculated by adding the two values.

16. Using UNION ALL and UNION DISTINCT

The anchor and recursive members are combined using a UNION operation.

16.1 UNION ALL

UNION ALL combines the results without removing duplicate rows. It is commonly used in recursive CTEs because it avoids the additional duplicate-elimination work performed by UNION DISTINCT.

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM number_sequence
    WHERE n < 5
)
SELECT n
FROM number_sequence;

16.2 UNION DISTINCT

UNION DISTINCT removes duplicate rows from the combined result. It can be useful in some recursive queries where duplicate elimination is part of the intended logic.

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n
    UNION DISTINCT
    SELECT n + 1
    FROM number_sequence
    WHERE n < 5
)
SELECT n
FROM number_sequence;

Both examples generate the same number sequence because every generated number is different. In hierarchical or graph-like queries, removing duplicate rows may help with duplicate paths only when the complete rows are identical. It does not automatically prevent every possible cycle, especially when each iteration adds a different level or path value.

17. Controlling Recursion and Preventing Infinite Loops

Every recursive CTE should be designed with a clear termination strategy. If the recursive member continues producing rows without reaching a stopping condition, the query may run for a long time or terminate only after reaching a server limit.

17.1 Use a WHERE Condition

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM number_sequence
    WHERE n < 100
)
SELECT n
FROM number_sequence;

The condition n < 100 limits the sequence to a maximum value of 100.

17.2 Use the Recursion-Depth Limit

MySQL provides the system variable cte_max_recursion_depth to limit the number of recursion levels. The default is commonly 1000, although server configuration may change it.

SELECT @@cte_max_recursion_depth;

You can set a session-level value when a legitimate query requires a different recursion depth:

SET SESSION cte_max_recursion_depth = 2000;

Only increase this limit when you understand the query's behavior and resource requirements. A higher limit does not fix an incorrect termination condition.

17.3 Set an Execution-Time Limit

For supported MySQL versions and query contexts, execution-time limits can provide an additional safeguard.

SET SESSION max_execution_time = 2000;

This sets a session-level timeout in milliseconds for applicable SELECT statements. Use the setting carefully because it may also affect other SELECT queries in the same session.

17.4 Limit Rows Where Supported

Beginning with MySQL 8.0.19, a LIMIT clause is supported in the recursive SELECT member. This can restrict row generation, but it should not replace correct recursion logic.

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1
    FROM number_sequence
    WHERE n < 10000
    LIMIT 10000
)
SELECT n
FROM number_sequence;

Check the syntax and behavior against your MySQL version before using recursive-member LIMIT in production queries.

18. Handling Cycles in Hierarchical Data

A cycle occurs when a chain of relationships eventually points back to a previously visited record. For example, if employee A reports to employee B and employee B reports to employee A, the hierarchy contains a cycle.

Cycles can cause recursive CTEs to revisit the same records repeatedly. This is especially important in graph-like data and in databases where relationships are not fully validated.

18.1 Common Cycle-Prevention Strategies

• Validate the hierarchy when inserting or updating parent-child relationships.
• Set an appropriate maximum depth for the traversal.
• Track visited identifiers in the recursive result.
• Check whether the next identifier already appears in the current path.
• Use appropriate unique constraints and foreign keys where they support the data model.

18.2 Example: Tracking a Visited Path

The following example demonstrates path tracking for a hierarchy whose identifiers are integers. The path is stored as text so that the query can check whether an employee ID has already been visited.

WITH RECURSIVE employee_hierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS hierarchy_level,
        CAST(
            CONCAT(',', employee_id, ',')
            AS CHAR(1000)
        ) AS visited_path
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.hierarchy_level + 1,
        CONCAT(eh.visited_path, e.employee_id, ',')
    FROM employees AS e
    JOIN employee_hierarchy AS eh
        ON e.manager_id = eh.employee_id
    WHERE eh.hierarchy_level < 20
      AND LOCATE(
          CONCAT(',', e.employee_id, ','),
          eh.visited_path
      ) = 0
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    hierarchy_level,
    visited_path
FROM employee_hierarchy
ORDER BY hierarchy_level, employee_id;

The query stores visited employee IDs in a comma-delimited path. Before following a relationship, it checks that the next ID is not already present in that path. The hierarchy-level condition provides an additional depth limit.

Important: The path column must be wide enough for the expected hierarchy. The example uses integer IDs and comma delimiters; other identifier formats may require a different encoding. Path tracking adds work and should be tested with realistic data.

19. Common Errors When Writing Recursive CTEs

19.1 Forgetting WITH RECURSIVE

A recursive CTE must use WITH RECURSIVE.

Incorrect:

WITH number_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM number_sequence
    WHERE n < 5
)
SELECT n
FROM number_sequence;

Correct:

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM number_sequence
    WHERE n < 5
)
SELECT n
FROM number_sequence;

19.2 Missing the Termination Condition

Without a stopping condition, the recursive member may continue producing rows until a server limit or timeout stops execution.

Best practice: Add a clear condition such as n < 10, a maximum hierarchy level, or a cycle-prevention check.

19.3 Mismatched Column Counts

The anchor and recursive members must return the same number of columns, in the same order, with compatible types.

Incorrect:

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1, 'extra'
    FROM number_sequence
    WHERE n < 5
)
SELECT *
FROM number_sequence;

The recursive member returns two columns while the anchor returns one.

19.4 Data Type or Width Problems

MySQL determines recursive CTE result-column types from the non-recursive anchor member. If recursive rows require wider values, the anchor may need an explicit cast or a wider data type.

For example, a path that starts as a short string may become longer during recursion. Cast the anchor path to a suitable character width before appending additional identifiers.

19.5 Incorrect Join Direction

When traversing an employee hierarchy downward, join each employee's manager ID to the current hierarchy employee ID. When traversing upward, join the manager's employee ID to the current row's manager ID.

-- Downward traversal
ON e.manager_id = eh.employee_id

-- Upward traversal
ON m.employee_id = mc.manager_id

19.6 Assuming UNION DISTINCT Prevents All Cycles

UNION DISTINCT removes duplicate complete rows. If recursive rows contain a changing depth or path column, revisiting the same identifier may still produce a different row and therefore survive duplicate elimination. Use explicit cycle detection where necessary.

19.7 Using Unsupported Constructs in the Recursive Member

MySQL restricts constructs inside the recursive SELECT member. Aggregate functions, window functions, GROUP BY, ORDER BY, and DISTINCT are not permitted there under the documented recursive-CTE rules. Depending on the version, LIMIT has specific support beginning with MySQL 8.0.19.

If aggregation or sorting is needed, consider performing it in the outer query or in a separate query layer where permitted.

20. Recursive CTEs Compared With Other Approaches

20.1 Recursive CTE vs Self-Join

A self-join is suitable when the relationship depth is fixed. For example, retrieving an employee and their direct manager requires only one self-join.

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

However, retrieving managers across an unknown number of levels would require repeated self-joins if no recursive technique were used. A recursive CTE is usually easier to maintain for variable-depth traversal.

21. Practical Use Cases of Recursive CTEs

21.1 Organizational Hierarchies

Retrieve all employees under a manager, identify the management chain, calculate hierarchy levels, and explore reporting structures.

21.2 Product Categories

Retrieve all descendants of a product category, display nested categories, and construct category navigation structures.

21.3 Folder and File Systems

Traverse nested folders and retrieve all descendants of a selected folder when the folder relationships are stored in a parent-child table.

21.4 Bill of Materials

Explore multilevel product components and subassemblies. Quantity calculations may require multiplying quantities along paths and aggregating repeated components.

21.5 Date and Number Generation

Create sequences for reporting, scheduling, calendars, testing datasets, and date-gap analysis without manually listing every value.

21.6 Dependency Analysis

Trace direct and indirect task dependencies, investigate upstream prerequisites, and explore chains of related records. Cycle detection is important when the relationship model permits circular dependencies.

21.7 Network and Graph Exploration

Follow linked records through a network of relationships. For graph-like data, account for repeated paths, cycles, and potentially large result sets.

22. Performance Optimization and Best Practices

22.1 Index Relationship Columns

For hierarchical queries, index the columns used to locate parent-child relationships. In the employees example, manager_id is used to find employees who report to a given manager.

CREATE INDEX idx_employees_manager
ON employees(manager_id);

An index can improve lookup performance, but the benefit depends on table size, data distribution, existing indexes, and the execution plan.

22.2 Keep the Anchor Result Small

Start recursion from only the required root record or set of root records. An unnecessarily broad anchor can create a large recursive result and increase execution cost.

22.3 Restrict the Recursive Member

Use relevant join conditions and filtering rules to avoid generating unrelated rows. Add a depth limit or cycle detection when appropriate.

22.4 Select Only Necessary Columns

Avoid carrying large or unnecessary columns through every recursive iteration. Keep the recursive result focused on the identifiers and attributes needed for traversal and output.

22.5 Use EXPLAIN

EXPLAIN
WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, employee_name, manager_id, 0 AS hierarchy_level
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id,
           eh.hierarchy_level + 1
    FROM employees AS e
    JOIN employee_hierarchy AS eh
        ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;

Use EXPLAIN to inspect the query plan and understand how MySQL accesses the underlying tables. Benchmark queries using representative data before deciding whether a different approach is necessary.

22.6 Consider Result Size

A recursive CTE can produce a large number of rows, especially when a hierarchy is broad or a graph contains many paths. Large intermediate results may consume memory and temporary-table resources.

Consider whether the task needs every descendant, only a specific level, a particular path, or an aggregate result. Limiting the result to the actual requirement can reduce unnecessary work.

23. Practical Project: Organizational Hierarchy Explorer

Project Objective

Create a recursive SQL query that starts from a selected manager and returns all direct and indirect reports, including the depth of each employee in the hierarchy.

Task 1: Retrieve All Reports Under Rahul

WITH RECURSIVE employee_hierarchy AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS hierarchy_level
    FROM employees
    WHERE employee_id = 2

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.hierarchy_level + 1
    FROM employees AS e
    JOIN employee_hierarchy AS eh
        ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    hierarchy_level
FROM employee_hierarchy
ORDER BY hierarchy_level, employee_id;

The result begins with Rahul at level 0 and includes employees reporting directly or indirectly to Rahul.

Task 2: Find the Management Chain for Neha

WITH RECURSIVE management_chain AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        0 AS hierarchy_level
    FROM employees
    WHERE employee_id = 5

    UNION ALL

    SELECT
        m.employee_id,
        m.employee_name,
        m.manager_id,
        mc.hierarchy_level + 1
    FROM employees AS m
    JOIN management_chain AS mc
        ON m.employee_id = mc.manager_id
)
SELECT
    employee_id,
    employee_name,
    manager_id,
    hierarchy_level
FROM management_chain
ORDER BY hierarchy_level;

Task 3: Generate a Calendar for One Month

WITH RECURSIVE calendar AS (
    SELECT DATE('2026-02-01') AS calendar_date

    UNION ALL

    SELECT calendar_date + INTERVAL 1 DAY
    FROM calendar
    WHERE calendar_date < DATE('2026-02-28')
)
SELECT calendar_date
FROM calendar
ORDER BY calendar_date;

This query generates every date from February 1 through February 28, 2026.

Task 4: Validate the Results

1. Verify that the anchor member starts from the intended employee or date.
2. Check that the recursive join follows the correct relationship direction.
3. Confirm that the termination condition prevents unwanted recursion.
4. Test the query with records that have no children or no manager.
5. Check whether duplicate paths or cycles are possible.
6. Review the execution plan and test with realistic data volumes.

24. Practice Exercises

1. Write a recursive CTE to generate numbers from 1 to 50.
2. Generate all even numbers between 10 and 100.
3. Create a recursive CTE that generates all dates in March 2026.
4. Retrieve all employees reporting directly or indirectly to a selected manager.
5. Find the full management chain for a given employee.
6. Build a recursive CTE that returns all subcategories under a selected product category.
7. Generate a Fibonacci sequence containing the first 15 values.
8. Modify a hierarchy query to include a level number for each record.
9. Add path tracking to a recursive CTE to prevent revisiting identifiers.
10. Compare a recursive CTE with a self-join for retrieving a direct manager.
11. Use EXPLAIN to inspect the execution plan of a hierarchy traversal.
12. Explain what happens when a recursive CTE has no effective termination condition.

25. Interview Questions and Answers

Q1. What is a recursive CTE in MySQL?

Answer: A recursive CTE is a named query result that refers to itself to generate additional rows. It is useful for hierarchical data, sequences, date ranges, and multilevel relationships.

Q2. Which keyword is required for a recursive CTE?

Answer: The query must use WITH RECURSIVE when defining a recursive CTE.

Q3. What is an anchor member?

Answer: The anchor member is the initial, non-recursive query that creates the first row or set of rows used to start the recursive process.

Q4. What is a recursive member?

Answer: The recursive member refers to the CTE itself and generates additional rows based on previously generated rows.

Q5. How does recursion terminate?

Answer: Recursion normally ends when the recursive member produces no additional rows. A well-designed query includes an appropriate stopping condition, and MySQL also provides recursion-depth and execution-time safeguards.

Q6. Which MySQL versions support recursive CTEs?

Answer: Recursive CTEs are supported in MySQL 8.0 and later.

Q7. What is the difference between a regular CTE and a recursive CTE?

Answer: A regular CTE does not refer to itself. A recursive CTE refers to its own name in the recursive member to generate additional rows.

Q8. Can recursive CTEs be used to generate dates?

Answer: Yes. The anchor member can define the starting date, while the recursive member adds one day or another interval until the ending condition is reached.

Q9. What is the purpose of UNION ALL in a recursive CTE?

Answer: UNION ALL combines the anchor and recursive results without removing duplicates. It is commonly used because it avoids the duplicate-elimination work of UNION DISTINCT.

Q10. How can cycles be prevented in a hierarchy?

Answer: Use valid data constraints, visited-path tracking, explicit cycle checks, and appropriate recursion limits. UNION DISTINCT alone does not prevent every cycle when rows differ in other columns.

Q11. What is cte_max_recursion_depth?

Answer: It is a MySQL system variable that limits the number of recursive CTE levels. Its default is commonly 1000, but server configuration can change it.

Q12. Can recursive CTEs replace all self-joins?

Answer: No. Self-joins are often suitable for fixed-depth relationships, while recursive CTEs are useful for unknown or variable-depth relationships.

Q13. What is a common use case for recursive CTEs?

Answer: Common use cases include employee hierarchies, nested categories, folder structures, number sequences, date generation, and multilevel component relationships.

Q14. Why can a recursive CTE produce an error about column types or data truncation?

Answer: MySQL determines recursive CTE result-column types from the anchor member. If later recursive rows need wider values, the anchor may need an explicit cast or a wider data type.

26. Quick Revision Checklist

• A CTE is a named result set available within one SQL statement.
• A recursive CTE refers to itself.
• Use WITH RECURSIVE to define recursive CTEs.
• The anchor member generates the initial rows.
• The recursive member generates additional rows.
• The anchor and recursive members must return compatible column structures.
• Use an appropriate termination condition.
• Use recursive CTEs for hierarchies, date ranges, sequences, and multilevel relationships.
• Use UNION ALL when duplicate elimination is unnecessary.
• Remember that UNION DISTINCT does not automatically prevent every cycle.
• Use indexes on relevant relationship columns.
• Consider recursion depth, execution time, and result size.
• Use path tracking or other cycle checks where necessary.
• Use EXPLAIN to inspect the execution plan.
• Test recursive queries against realistic data before production use.

27. Conclusion

Recursive CTEs are a powerful MySQL feature for querying data that requires repeated traversal or step-by-step generation. By combining an anchor member with a recursive member, developers can generate sequences, build date ranges, retrieve management chains, explore product categories, and investigate multilevel dependencies without writing a fixed number of joins.

To write reliable recursive CTEs, understand the anchor and recursive members, choose the correct relationship direction, ensure compatible column types, and define a clear stopping condition. Pay special attention to cycles, recursion limits, indexes, and result size when working with complex or large datasets.

Continue Learning: Explore the MySQL Training Course to strengthen your SQL and database skills, or register for a course demo to learn more about the training.